package com.wuye.repository;

import com.wuye.model.repair.RepairOrder;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * Created by jiawenlong on 2016/7/26.
 */
public interface RepairOrderRepository extends PagingAndSortingRepository<RepairOrder, Long> {

    String conditionSql = " where " +
            "IF(:qf = '', 1=1, " +
            "IF(:qf = 'id', o.id = :qv, " +
            "IF(:qf = 'connectionUserMobile', o.connectionUserMobile LIKE %:qv%," +
            "IF(:qf = 'connectionUserName', o.connectionUserName LIKE %:qv%," +
            "IF(:qf = 'connectionUserAddress', o.connectionUserAddress LIKE %:qv%,0=1" +
            ") ) ) ) ) and " +
            " IF(:orderState = -1,1=1,o.state=:orderState) and " +
            " IF(:repairerId = -1,1=1,o.repairPersonId=:repairerId) and " +
            " (o.addTime >= :dv AND o.addTime <= :dv2) ";

    RepairOrder findById(long id);

    @Query(nativeQuery = true, value = "select o.id,c.name,o.content,o.state,o.connectionUserName,o.connectionUserMobile,o.connectionUserAddress," +
            "o.addTime ,c.`name` as categoryName ,o.repairPersonId,p.`name` as repairPersonName,p.mobile as repairPersonMobile,o.remark  ,c2.name as secondCateName " +
            " from RepairOrder o LEFT JOIN Category c ON o.categoryId=c.id LEFT JOIN RepairPerson p on o.repairPersonId=p.id  left join Category c2 on o.categorySecondId=c2.id "
            + conditionSql + "  order by o.id desc LIMIT :st, :ps")
    List<Object[]> queryOrders(@Param("qf") String qField, @Param("qv") String qVal,
                               @Param("st") int start, @Param("ps") int pageSize, @Param("orderState") int orderState, @Param("repairerId") long repairerId, @Param("dv") String dv, @Param("dv2") String dv2);

    @Query(nativeQuery = true, value = "select o.id,o.connectionUserName,o.connectionUserMobile,o.connectionUserAddress," +
            "o.addTime ,o.repairPersonId,p.`name` as repairPersonName,p.mobile as repairPersonMobile,o.endTime,o.feednack " +
            " from RepairOrder o LEFT JOIN Category c ON o.categoryId=c.id LEFT JOIN RepairPerson p on o.repairPersonId=p.id "
            + conditionSql + " and o.visitState=:visit  order by o.id desc LIMIT :st, :ps")
    List<Object[]> queryFeedbackOrders(@Param("qf") String qField, @Param("qv") String qVal,
                                       @Param("st") int start, @Param("ps") int pageSize, @Param("orderState") int orderState, @Param("repairerId") long repairerId, @Param("dv") String dv, @Param("dv2") String dv2, @Param("visit") int visit);

    @Query(nativeQuery = true, value = "select count(DISTINCT o.id) " +
            "from   RepairOrder o LEFT JOIN Category c ON o.categoryId=c.id LEFT JOIN RepairPerson p on o.repairPersonId=p.id "
            + conditionSql +" and  o.visitState=:visit")
    int queryFeedbackOrdersCount(@Param("qf") String qField, @Param("qv") String qVal,
                                 @Param("orderState") int orderState, @Param("repairerId") long repairerId, @Param("dv") String dv, @Param("dv2") String dv2, @Param("visit") int visit);

    @Query(nativeQuery = true, value = "select o.id,c.name,o.content,o.state,o.connectionUserName,o.connectionUserMobile,o.connectionUserAddress," +
            "o.addTime ,c.`name` as categoryName ,o.repairPersonId,p.`name` as repairPersonName,p.mobile as repairPersonMobile,o.remark ,c2.name as secondCateName " +
            " from RepairOrder o LEFT JOIN Category c ON o.categoryId=c.id LEFT JOIN RepairPerson p on o.repairPersonId=p.id  left join Category c2 on o.categorySecondId=c2.id "
            + conditionSql + "  order by o.id desc ")
    List<Object[]> queryExportOrders(@Param("qf") String qField, @Param("qv") String qVal,
                                     @Param("orderState") int orderState, @Param("repairerId") long repairerId, @Param("dv") String dv, @Param("dv2") String dv2);


    @Query(nativeQuery = true, value = "select count(DISTINCT o.id) " +
            "from   RepairOrder o LEFT JOIN Category c ON o.categoryId=c.id LEFT JOIN RepairPerson p on o.repairPersonId=p.id "
            + conditionSql)
    int queryOrdersCount(@Param("qf") String qField, @Param("qv") String qVal,
                         @Param("orderState") int orderState, @Param("repairerId") long repairerId, @Param("dv") String dv, @Param("dv2") String dv2);

    @Query(nativeQuery = true, value = "select o.id,c.name,o.content,o.state,o.connectionUserName,o.connectionUserMobile,o.connectionUserAddress," +
            "o.addTime ,c.`name` as categoryName ,o.repairPersonId,p.`name` as repairPersonName,p.mobile as repairPersonMobile,o.remark,c2.name as secondCateName " +
            " from RepairOrder o LEFT JOIN Category c ON o.categoryId=c.id LEFT JOIN RepairPerson p on o.repairPersonId=p.id left join Category c2 on o.categorySecondId=c2.id where o.creater=? order by id desc")
    List<Object[]> findByCreater(String userId);


}
